/**
 * EditCourseInfoDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.EditCourseInfoDTO;
import pits.form.EditCourseInfoForm;
import pits.util.Constants;
import pits.util.Logger;

public class EditCourseInfoDAO {

	/**
	 * @return
	 */
	public EditCourseInfoDTO getData(String courseID) {
		EditCourseInfoDTO result = new EditCourseInfoDTO();
		List<String> companyNames = new ArrayList<String>();
		List<Integer> companyIDs = new ArrayList<Integer>();
		List<List<String>> departNames = new ArrayList<List<String>>();
		List<List<Integer>> departIDs = new ArrayList<List<Integer>>();
		List<String> eachComDepNames = null;
		List<Integer> eachComDepIDs = null;
		List<String> appNames = new ArrayList<String>();
		List<Integer> appIDs = new ArrayList<Integer>();
		List<String> langNames = new ArrayList<String>();
		List<Integer> langIDs = new ArrayList<Integer>();
		List<String> techNames = new ArrayList<String>();
		List<Integer> techIDs = new ArrayList<Integer>();
		List<String> frameNames = new ArrayList<String>();
		List<Integer> frameIDs = new ArrayList<Integer>();

		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement sqlStatement = null;
		ResultSet comResultSet = null;
		ResultSet departResultSet = null;
		ResultSet langResultSet = null;
		ResultSet appResultSet = null;
		ResultSet techResultSet = null;
		ResultSet frameResultSet = null;

		try {
			query = "SELECT * FROM company WHERE isDeleted=0";

			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);

			// new Logger().write(query);

			comResultSet = sqlStatement.executeQuery();

			while (comResultSet.next()) {
				query = "SELECT * FROM department" + " WHERE CompanyID=" + comResultSet.getInt("CompanyID")
				        + " AND isDeleted=0";

				// new Logger().write(query);

				sqlStatement = conn.prepareStatement(query);
				departResultSet = sqlStatement.executeQuery();

				eachComDepNames = new ArrayList<String>();
				eachComDepIDs = new ArrayList<Integer>();

				while (departResultSet.next()) {
					// new
					// Logger().write(departResultSet.getString("DepartName"));
					eachComDepNames.add(departResultSet.getString("DepartName"));
					eachComDepIDs.add(departResultSet.getInt("DepartID"));
				}
				// new Logger().write(comResultSet.getString("ComName"));
				companyNames.add(comResultSet.getString("ComName"));
				companyIDs.add(comResultSet.getInt("CompanyID"));
				departNames.add(eachComDepNames);
				departIDs.add(eachComDepIDs);
			}

			// new Logger().writeln("getComList: done");

			query = "SELECT * FROM language WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			langResultSet = sqlStatement.executeQuery();
			while (langResultSet.next()) {
				langNames.add(langResultSet.getString("Name"));
				langIDs.add(langResultSet.getInt("LangID"));
			}

			query = "SELECT * FROM application WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			appResultSet = sqlStatement.executeQuery();
			while (appResultSet.next()) {
				appNames.add(appResultSet.getString("Name"));
				appIDs.add(appResultSet.getInt("AppID"));
			}

			query = "SELECT * FROM technology WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			techResultSet = sqlStatement.executeQuery();
			while (techResultSet.next()) {
				techNames.add(techResultSet.getString("Name"));
				techIDs.add(techResultSet.getInt("TechID"));
			}

			query = "SELECT * FROM framework WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			frameResultSet = sqlStatement.executeQuery();
			while (frameResultSet.next()) {
				frameNames.add(frameResultSet.getString("Name"));
				frameIDs.add(frameResultSet.getInt("FrameID"));
			}

			// new Logger().writeln("getCommonData: done");

			/**
			  * 
			  */
			query = "SELECT * FROM company cm, department d, course c" + " WHERE c.CourseID=" + courseID
			        + " AND c.DepartID=d.DepartID" + " AND d.CompanyID=cm.CompanyID" + " AND cm.isDeleted=0";

			// new pits.util.Logger().writeln(query);

			sqlStatement = conn.prepareStatement(query);
			comResultSet = sqlStatement.executeQuery();
			if (comResultSet.next()) {
				result.setCurrentComID(comResultSet.getInt("CompanyID"));
				result.setCourseName(comResultSet.getString("c.Name"));
				result.setCurrentDepartID(comResultSet.getInt("DepartID"));
				result.setDescription(comResultSet.getString("c.Description"));
				result.setDateIssused(comResultSet.getString("c.DateIssused"));
			}

			// new Logger().writeln("getCurrent: done");

			query = "SELECT * FROM technology t, course_technology ct" + " WHERE ct.CourseID=" + courseID
			        + " AND t.TechID=ct.TechID" + " AND t.isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			techResultSet = sqlStatement.executeQuery();
			String currentTechName = "";
			String currentTechID = "";
			while (techResultSet.next()) {
				currentTechName += ", " + techResultSet.getString("Name");
				currentTechID += ":" + techResultSet.getInt("TechID");
			}
			if (!currentTechName.equals("")) {
				currentTechName = currentTechName.substring(2);
				currentTechID = currentTechID.substring(1);
			}
			result.setCurrentTechName(currentTechName);
			result.setCurrentTechID(currentTechID);

			// new Logger().writeln("getTech: done");

			query = "SELECT * FROM framework f, course_framework cf" + " WHERE cf.CourseID=" + courseID
			        + " AND f.FrameID=cf.FrameID" + " AND f.isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			frameResultSet = sqlStatement.executeQuery();
			String currentFrameName = "";
			String currentFrameID = "";
			while (frameResultSet.next()) {
				currentFrameName += ", " + frameResultSet.getString("Name");
				currentFrameID += ":" + frameResultSet.getInt("FrameID");
			}
			if (!currentFrameName.equals("")) {
				currentFrameName = currentFrameName.substring(2);
				currentFrameID = currentFrameID.substring(1);
			}
			result.setCurrentFrameName(currentFrameName);
			result.setCurrentFrameID(currentFrameID);

			// new Logger().writeln("getFrame: done");

			query = "SELECT * FROM application a, course_application ca" + " WHERE ca.CourseID=" + courseID
			        + " AND a.AppID=ca.AppID" + " AND a.isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			appResultSet = sqlStatement.executeQuery();
			String currentAppName = "";
			String currentAppID = "";
			while (appResultSet.next()) {
				currentAppName += ", " + appResultSet.getString("Name");
				currentAppID += ":" + appResultSet.getInt("AppID");
			}
			if (!currentAppName.equals("")) {
				currentAppName = currentAppName.substring(2);
				currentAppID = currentAppID.substring(1);
			}
			result.setCurrentAppName(currentAppName);
			result.setCurrentAppID(currentAppID);

			// new Logger().writeln("getApp: done");

			query = "SELECT * FROM language l, course_language cl" + " WHERE cl.CourseID=" + courseID
			        + " AND l.LangID=cl.LangID" + " AND l.isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			langResultSet = sqlStatement.executeQuery();
			String currentLangName = "";
			String currentLangID = "";
			while (langResultSet.next()) {
				currentLangName += ", " + langResultSet.getString("Name");
				currentLangID += ":" + langResultSet.getInt("LangID");
			}
			if (!currentLangName.equals("")) {
				currentLangName = currentLangName.substring(2);
				currentLangID = currentLangID.substring(1);
			}
			result.setCurrentLangName(currentLangName);
			result.setCurrentLangID(currentLangID);

			// new Logger().writeln("getLang: done");

		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				frameResultSet.close();
				techResultSet.close();
				langResultSet.close();
				departResultSet.close();
				comResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		result.setCompanyNames(companyNames);
		result.setCompanyIDs(companyIDs);
		result.setDepartNames(departNames);
		result.setDepartIDs(departIDs);
		result.setAppIDs(appIDs);
		result.setAppNames(appNames);
		result.setFrameIDs(frameIDs);
		result.setFrameNames(frameNames);
		result.setLangIDs(langIDs);
		result.setLangNames(langNames);
		result.setTechIDs(techIDs);
		result.setTechNames(techNames);

		// new Logger().writeln("getData: done");

		return result;
	}

	public void update(EditCourseInfoForm formCourseInfo) {
		Connection connection = null;
		String query = null;

		PreparedStatement sqlStatement = null;
		ResultSet resultSet = null;

		String department = null;

		try {
			connection = pits.util.Connection.getConnection();

			department = formCourseInfo.getDepartment();

			if (department.charAt(0) == ':') {
				query = "INSERT INTO department (DepartName, ParentID, CompanyID, isDeleted) " + "VALUES ('"
				        + department.substring(1) + "', 0, " + formCourseInfo.getCompanyID() + ", 0)";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();

				query = "SELECT * FROM department " + "WHERE DepartName='" + department.substring(1) + "' "
				        + "AND isDeleted=0";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				resultSet = sqlStatement.executeQuery();
				if (resultSet.next()) {
					department = resultSet.getString("DepartID");
				}
			}

			query = "UPDATE course " + "SET Description='" + formCourseInfo.getDescription() + "', " + "Name='"
			        + formCourseInfo.getCourseName() + "', " + "DepartID=" + department + ", " + "DateIssused='"
			        + formCourseInfo.getDateIssused() + "' " + "WHERE CourseID=" + formCourseInfo.getCourseID() + " "
			        + "AND isDeleted=0";

			// new pits.util.Logger().write(query);

			sqlStatement = connection.prepareStatement(query);
			sqlStatement.executeUpdate();

			/**
			 * Language list
			 */
			for (int i = 0; i < formCourseInfo.getLangNames().size(); i++) {
				query = "INSERT INTO language (Name, isDeleted) " + "VALUE ('" + formCourseInfo.getLangNames().get(i)
				        + "', 0)";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();

				query = "SELECT * FROM language " + "WHERE Name='" + formCourseInfo.getLangNames().get(i) + "' "
				        + "AND isDeleted=0";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				resultSet = sqlStatement.executeQuery();
				resultSet.next();

				query = "INSERT INTO course_language " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + resultSet.getInt("LangID") + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			for (int i = 0; i < formCourseInfo.getLangIDs().size(); i++) {

				query = "INSERT IGNORE course_language " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + formCourseInfo.getLangIDs().get(i).intValue() + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			/**
			 * Application list
			 */
			for (int i = 0; i < formCourseInfo.getAppNames().size(); i++) {
				query = "INSERT INTO application (Name, isDeleted) " + "VALUE ('" + formCourseInfo.getAppNames().get(i)
				        + "', 0)";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();

				query = "SELECT * FROM application " + "WHERE Name='" + formCourseInfo.getAppNames().get(i) + "' "
				        + "AND isDeleted=0";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				resultSet = sqlStatement.executeQuery();
				resultSet.next();

				query = "INSERT INTO course_application " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + resultSet.getInt("AppID") + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			for (int i = 0; i < formCourseInfo.getAppIDs().size(); i++) {

				query = "INSERT IGNORE course_application " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + formCourseInfo.getAppIDs().get(i).intValue() + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			/**
			 * Technnology list
			 */
			for (int i = 0; i < formCourseInfo.getTechNames().size(); i++) {
				query = "INSERT INTO technology (Name, isDeleted) " + "VALUE ('" + formCourseInfo.getTechNames().get(i)
				        + "', 0)";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();

				query = "SELECT * FROM technology " + "WHERE Name='" + formCourseInfo.getTechNames().get(i) + "' "
				        + "AND isDeleted=0";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				resultSet = sqlStatement.executeQuery();
				resultSet.next();

				query = "INSERT INTO course_technology " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + resultSet.getInt("TechID") + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			for (int i = 0; i < formCourseInfo.getTechIDs().size(); i++) {

				query = "INSERT IGNORE course_technology " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + formCourseInfo.getTechIDs().get(i).intValue() + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			/*
			 * Framework list
			 */
			for (int i = 0; i < formCourseInfo.getFrameNames().size(); i++) {
				query = "INSERT INTO framework (Name, isDeleted) " + "VALUE ('" + formCourseInfo.getFrameNames().get(i)
				        + "', 0)";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();

				query = "SELECT * FROM framework " + "WHERE Name='" + formCourseInfo.getFrameNames().get(i) + "' "
				        + "AND isDeleted=0";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				resultSet = sqlStatement.executeQuery();
				resultSet.next();

				query = "INSERT INTO course_framework " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + resultSet.getInt("FrameID") + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}

			for (int i = 0; i < formCourseInfo.getFrameIDs().size(); i++) {

				query = "INSERT IGNORE course_framework " + "VALUES (" + formCourseInfo.getCourseID() + ", "
				        + formCourseInfo.getFrameIDs().get(i).intValue() + ", " + "0" + ")";

				// new pits.util.Logger().writeln(query);

				sqlStatement = connection.prepareStatement(query);
				sqlStatement.executeUpdate();
			}
		} catch (Exception sqlEx) {
			new pits.util.Logger().writeln(sqlEx.toString());
		} finally {
			try {
				sqlStatement.close();
				connection.close();
			} catch (SQLException sqlEx) {
				new pits.util.Logger().writeln(sqlEx.toString());
			}
		}
	}
}
